-----------------------------------------------NGANH--------------------------------------
create proc them_nganh
 @id_nganh nvarchar(10),
 @ten_nganh nvarchar(50)
as
begin
	insert into nganh(id_nganh,ten_nganh) values (@id_nganh,@ten_nganh);
end


create proc nganh_load --SELECT * NGANH
as
begin
	select * from nganh
end
create proc nganh_load_theoma--SELECT * NGANH theo ma
@id_nganh nvarchar (10)
as
begin
	select * from nganh where id_nganh = @id_nganh
end

create proc nganh_xoa --xoa NGANH
@id_nganh nvarchar(10)
as
begin
	delete from nganh where id_nganh=@id_nganh
end

create proc nganh_update --update NGANH
@id_nganh nvarchar(10),
@ten_nganh nvarchar(50)
as
begin
	update nganh set ten_nganh=@ten_nganh where id_nganh= @id_nganh
end



------------------------------------------------NHANVIEN----------------------------------  
alter proc them_nhanvien
 @name_nhanvien nvarchar(50),
 @id_nganh nvarchar(10)
as
begin
	declare @ma_next nchar(10) -- max 7 ki tu
	declare @max int
	select @max = count(id_nhanvien) + 1 -- MACV ke tiep dang 'CVxxxxx'
	from dbo.nhanvien
	where id_nhanvien like 'NV%'
	-- tao maloaive ke tiep
	if(@max >0 and @max <10)
		set @ma_next = 'NV'+ right( '00' + cast(@max as nchar(10)) , 5) 
	if(@max >=10 and @max <100)
		set @ma_next = 'NV0'+ right( '0' + cast(@max as nchar(10)) , 5)
	if(@max >=100 and @max <1000)
		set @ma_next = 'NV0'+ right( '' + cast(@max as nchar(10)) , 5)
	-- kiem ta ton tai
	while(exists(select id_nhanvien from dbo.nhanvien where id_nhanvien= @ma_next))
	begin
	set @max = @max + 1
	set @ma_next = 'NV0'+ right( '0' + cast(@max as nchar(10)), 5)
    end
	insert into nhanvien values (@ma_next,@name_nhanvien,@id_nganh);
end


create proc nhanvien_load --SELECT * nhanvien
as
begin
	select * from nhanvien
end
create proc nhanvien_load_theoma --SELECT * nhanvien theo ma
@id_nhanvien nvarchar(10)
as
begin
	select * from nhanvien where id_nhanvien=@id_nhanvien
end
create proc nhanvien_load_theonganh --SELECT * nhanvien theo nganh
@id_nganh nvarchar(10)
as
begin
	select * from nhanvien where id_nganh=@id_nganh
end

create proc nhanvien_xoa --xoa nhanvien
@id_nhanvien nvarchar(10)
as
begin
	delete nhanvien where id_nhanvien=@id_nhanvien
end

create proc nhanvien_update --update nhanvien
@id_nhanvien nvarchar(10),
@ten_nhanvien nvarchar(50),
@id_nganh nvarchar(10)
as
begin
	update nhanvien set name_nhanvien=@ten_nhanvien, id_nganh=@id_nganh where id_nhanvien= @id_nhanvien
end

-------------------------------------------------ung vien--------------------------------------


create proc ungvien_load --SELECT * ungvien
as
begin
	select * from ungvien
end

create proc ungvien_xoa --xoa ungvien
@id_ungvien nvarchar(10)
as
begin
	delete ungvien where id_ungvien=@id_ungvien
end

create proc ungvien_update --update ungvien
@id_ungvien nvarchar(10),
@name_ungvien nvarchar(50),
@id_nganh nvarchar(10)
as
begin
	update ungvien set name_ungvien=@name_ungvien, id_nganh=@id_nganh where id_ungvien= @id_ungvien
end
---------------------------------------------chi tiet phong van-----------------------------------


alter proc ctpv_load --SELECT  Chitietphongvan 2 tham so id_nhanvien va id_ ungvien
@id_nhanvien nvarchar(10),
@id_ungvien nvarchar(10)
as
begin
	select * from chitietphongvan where id_nhanvien=@id_nhanvien and id_ungvien=@id_ungvien
end



execute them_nhanvien 'son','cntt'

select * from nhanvien